package com.person.jc.service.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.person.jc.common.SQLSpell;
import com.person.jc.service.Service;
import com.person.jc.utils.CreateCode;
import com.person.jc.utils.Utils;

public class BsOrderService extends Service {
	
	public final static BsOrderService me = new BsOrderService();
	
	public List<Record> getList(Map<String,Object> map){
		String temp = "";
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select order_id,order_no,t1.cust_name,t.team_no,t.order_status,date_format(t.receive_date,'%Y-%m-%d') receive_date,t.link_man,t.link_mobile,t.link_phone," +
				"t.consignee_man,t.consignee_phone,service_user, ("+SQLSpell.userName("t.service_user")+") service_user_,date_format(t.opt_date,'%Y-%m-%d') opt_date, " +
				"t.opt_user, ("+SQLSpell.userName("t.opt_user")+") opt_user_, t.flow_id from bs_order t, bd_customer t1 where t.cust_id = t1.cust_id and order_type = ?");
		params.add(map.get("orderType"));
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderId")))){
			sql.append(" and t.order_id like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderNo")))){
			sql.append(" and t.order_no like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
			sql.append(" and t.cust_id = ? ");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("teamNo")))){
			sql.append(" and t.team_no like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate")))){
			sql.append(" and DATE_FORMAT(t.opt_date,'%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endDate")))){
			sql.append(" and DATE_FORMAT(t.opt_date,'%Y-%m-%d') <= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("serviceUser")))){
			sql.append(" and t.service_user = ? ");
			params.add(temp);
		}
		sql.append(" order by t.opt_date desc");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}
	
	public Record getOrderInfo(String orderId){
		String sql = "select t.*, ("+SQLSpell.userName("t.opt_user")+") opt_user_ from bs_order t where order_id = ? ";
		return Db.findFirst(sql, orderId);
	}
	
	public List<Record> getOrderDetail(String orderId, String taskType){
		String sql = "select * from bs_order_equipment where order_id = ? ";
		//维修任务分配明细列表
		if("wx".equals(taskType)){
			sql = "select oe.*,rc.confirm_status rc_confirm, pc.confirm_status pc_confirm, rd.* from bs_order_equipment oe\n" +
					"left join bs_repair_reg_log rl on oe.equ_id = rl.equ_id\n" +
					"left join bs_parts_confirm_log pc on rl.log_id = pc.log_id\n" +
					"left join bs_repair_confirm_log rc on rl.log_id = rc.log_id\n" +
					"left join\n" +
					"(SELECT d.log_id,\n" +
					"       group_concat(DISTINCT f.fault_name ORDER BY f.fault_name ASC SEPARATOR '; ') fault_name,\n" +
					"       group_concat(DISTINCT concat(p.parts_name, '[', p.parts_code, ']') ORDER BY p.parts_name ASC,  p.parts_code ASC SEPARATOR '; ') parts_name\n" +
					"  FROM bs_repair_check_detail d\n" +
					"  LEFT JOIN bd_fault_info f\n" +
					"    ON d.fault_id = f.fault_id\n" +
					"  LEFT JOIN bd_device_parts p\n" +
					"    ON d.parts_id = p.parts_id\n" +
					" GROUP BY d.log_id) rd on rl.log_id = rd.log_id\n" +
					"where 1=1\n" +
					"and oe.order_id =?";
		}
		return Db.find(sql, orderId);
	}
	
	public Record findEquipmentById(String equId) {
		return Db.findById("bs_order_equipment", "equ_id", equId);
	}
	
	public String save(String orderType, Map<String,Object> map){
		String orderId = CreateCode.createOrderId(orderType);
		String orderNo = CreateCode.createOrderNo(orderType);
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("insert into bs_order (order_id,order_no,cust_id,team_no,receive_date," +
				"link_man,link_mobile,link_phone,link_fax,address,consignee_man,consignee_phone,opt_user,opt_date," +
				"service_user,order_type,order_status) values(?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,1)");
		params.add(orderId);
		params.add(orderNo);
		params.add(map.get("custId"));
		params.add(map.get("teamNo"));
		params.add(map.get("receiveDate"));
		params.add(map.get("linkMan"));
		params.add(map.get("linkMobile"));
		params.add(map.get("linkPhone"));
		params.add(map.get("linkFax"));
		params.add(map.get("address"));
		params.add(map.get("consigneeMan"));
		params.add(map.get("consigneePhone"));
		params.add(map.get("optUser"));
		params.add(map.get("serviceUser"));
		params.add(orderType);
		Db.update(sql.toString(), Utils.getObjParam(params));
		super.setCRUD(getMenuName(orderType), "C", "新增订单：" + orderId);
		return orderId;
	}
	
	public void update(String orderType, Map<String,Object> map){
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("update bs_order set cust_id = ?,team_no = ?,receive_date = ?," +
				"link_man = ?,link_mobile = ?,link_phone = ?,link_fax = ?,address = ?,consignee_man = ?," +
				"consignee_phone = ?,service_user = ? where order_id = ?");
		params.add(map.get("custId"));
		params.add(map.get("teamNo"));
		params.add(map.get("receiveDate"));
		params.add(map.get("linkMan"));
		params.add(map.get("linkMobile"));
		params.add(map.get("linkPhone"));
		params.add(map.get("linkFax"));
		params.add(map.get("address"));
		params.add(map.get("consigneeMan"));
		params.add(map.get("consigneePhone"));
		params.add(map.get("serviceUser"));
		params.add(map.get("orderId"));
		Db.update(sql.toString(), Utils.getObjParam(params));
		super.setCRUD(getMenuName(orderType), "U", "修改订单：" + map.get("orderId"));
	}
	
	public void delete(String orderType, String orderId){
		String sql = "delete from bs_order where order_id = ? ";
		Db.update(sql, orderId);
		super.setCRUD(getMenuName(orderType), "D", "删除订单：" + orderId);
	}
	
	public void deleteDetail(String orderType, String orderId){
		String sql = "delete from bs_order_equipment where order_id = ?";
		Db.update(sql, orderId);
		super.setCRUD(getMenuName(orderType), "D", "删除订单仪器：" + orderId);
	}
	
	public void saveDetail(String orderType, String orderId, Object[][] params, int length){
		String sql = "insert into bs_order_equipment(order_id,device_type,device_name,device_no," +
				"device_model,device_manufactor,gas_type,pack_status,look_status,file_status,issue_desc," +
				"if_cert,remark,opt_user,volume_category,manufacture_date) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Db.batch(sql, params, length);
		super.setCRUD(getMenuName(orderType), "C", "新增订单仪器：" + orderId);
	}
	
	private static String getMenuName(String type){
		String menuName = "检测接单";
		if("2".equals(type)){
			menuName = "维修接单";
		}
		return menuName;
	}

	
	public void updateFlow(String orderId,String flow) {
		String sql = "update bs_order set flow_id = ? where order_id = ?";
		Db.update(sql, flow, orderId);
		super.setCRUD("订单流程节点", "U", "订单流程节点变更：" + orderId+"，"+flow);
		
	}

	public Record uniqueDeivceNo(String orderId, String params) {
		params = params.replace(",", "','");
		String sql = "select GROUP_CONCAT(t.device_no) devicenos from bs_order_equipment t where t.device_no in ('"+params+"')";
		if(Utils.isNotEmpty(orderId)){
			sql += " and t.order_id != '"+orderId+"'";
		}
		return Db.findFirst(sql);
	}

	public void updateOrderStatus(String orderId, int status) {
		String sql = "update bs_order set order_status=? where order_id = ?";
		Db.update(sql, status, orderId);
		super.setCRUD("订单完成", "U", "订单完成变更：" + orderId);
		
	}

	public List<Record> getListCount(int type) {
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.order_id FROM bs_order t WHERE order_type = ? AND t.flow_id=9");
		params.add(type);		
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}

}